// soil-data.service.ts
import { Provide, Inject } from '@midwayjs/core';
import { InjectEntityModel } from '@midwayjs/typeorm';
import { Repository, Between, Like } from 'typeorm';
import { SoilDataEntity } from '../entity/soilData.entity';
import { CreateSoilDataDTO, UpdateSoilDataDTO, QuerySoilDataDTO, ImportSoilDataDTO } from '../dto/soilDataDto';
import * as XLSX from 'xlsx';

@Provide()
export class SoilDataService {
  @InjectEntityModel(SoilDataEntity)
  soilDataModel: Repository<SoilDataEntity>;

  /**
   * 创建土壤数据记录
   */
  async create(data: CreateSoilDataDTO): Promise<SoilDataEntity> {
    const entity = this.soilDataModel.create(data);
    return await this.soilDataModel.save(entity);
  }

  /**
   * 更新土壤数据记录
   */
  async update(data: UpdateSoilDataDTO): Promise<SoilDataEntity> {
    const { id, ...updateData } = data;
    await this.soilDataModel.update(id, updateData);
    return await this.soilDataModel.findOne({ where: { id } });
  }

  /**
   * 删除土壤数据记录
   */
  async delete(id: number): Promise<boolean> {
    const result = await this.soilDataModel.delete(id);
    return result.affected > 0;
  }

  /**
   * 根据ID查询单条记录
   */
  async findById(id: number): Promise<SoilDataEntity> {
    return await this.soilDataModel.findOne({ where: { id } });
  }

  /**
   * 分页查询土壤数据
   */
  async findByPage(query: QuerySoilDataDTO): Promise<{
    list: SoilDataEntity[];
    total: number;
    current: number;
    pageSize: number;
  }> {
    const { current = 1, pageSize = 10, startTime, endTime, ...where } = query;
    
    const queryBuilder = this.soilDataModel.createQueryBuilder('soil');
    
    // 处理时间范围查询
    if (startTime && endTime) {
      queryBuilder.andWhere('soil.time BETWEEN :startTime AND :endTime', {
        startTime,
        endTime,
      });
    }
    
    // 处理ID查询
    if (where.id) {
      queryBuilder.andWhere('soil.id = :id', { id: where.id });
    }
    
    // 获取总数
    const total = await queryBuilder.getCount();
    
    // 获取分页数据
    const list = await queryBuilder
      .orderBy('soil.time', 'DESC')
      .skip((current - 1) * pageSize)
      .take(pageSize)
      .getMany();
    
    return {
      list,
      total,
      current,
      pageSize,
    };
  }

  /**
   * 批量导入Excel数据
   */
  async importFromExcel(buffer: Buffer): Promise<{ total: number; success: number; fail: number }> {
    // 解析Excel文件
    const workbook = XLSX.read(buffer, { type: 'buffer' });
    const sheetName = workbook.SheetNames[0];
    const sheet = workbook.Sheets[sheetName];
    const rawData = XLSX.utils.sheet_to_json(sheet);
    
    const entities: SoilDataEntity[] = [];
    const errorRows: number[] = [];
    
    // 转换Excel数据为实体对象
    rawData.forEach((row: any, index: number) => {
      try {
        const entity = new SoilDataEntity();
        
        // 时间处理，假设Excel中有"时间"列或"time"列
        if (row['时间'] || row['time']) {
          entity.time = new Date(row['时间'] || row['time']);
        } else {
          throw new Error('时间字段缺失');
        }
        
        // 电池电量
        if ('电池电量(%)' in row || 'batteryLevel' in row) {
          entity.batteryLevel = Number(row['电池电量(%)'] || row['batteryLevel']);
        }
        
        // 处理水分含量和温度数据
        for (let i = 10; i <= 100; i += 10) {
          if (`水分含量${i}cm(%)` in row || `moistureAt${i}cm` in row) {
            entity[`moistureAt${i}cm`] = Number(row[`水分含量${i}cm(%)`] || row[`moistureAt${i}cm`]);
          }
          
          if (`土壤温度${i}cm(℃)` in row || `temperatureAt${i}cm` in row) {
            entity[`temperatureAt${i}cm`] = Number(row[`土壤温度${i}cm(℃)`] || row[`temperatureAt${i}cm`]);
          }
        }
        
        // 地表温度
        if ('地表温度(℃)' in row || 'surfaceTemperature' in row) {
          entity.surfaceTemperature = Number(row['地表温度(℃)'] || row['surfaceTemperature']);
        }
        
        entities.push(entity);
      } catch (error) {
        errorRows.push(index + 2); // 加2是因为Excel行号从1开始，还有表头占一行
      }
    });
    
    // 批量保存有效数据
    let savedCount = 0;
    if (entities.length > 0) {
      const result = await this.soilDataModel.save(entities);
      savedCount = result.length;
    }
    
    return {
      total: rawData.length,
      success: savedCount,
      fail: errorRows.length,
    };
  }
  
  /**
   * 批量创建土壤数据
   */
  async batchCreate(data: ImportSoilDataDTO): Promise<{ success: number }> {
    const entities = this.soilDataModel.create(data.data);
    const result = await this.soilDataModel.save(entities);
    return { success: result.length };
  }
}
